{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 合并数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 堆叠合并数据 pd.concat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pd.concat()实现将两个表拼在一起。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "常用参数说明：\n",
    "\n",
    "参数名称 | 参数说明\n",
    "-------|---------------\n",
    "objs   |接收多个DataFrame,表示参与连接的对象\n",
    "axis   |连接轴，可选0或1，默认为0\n",
    "join   |连接方式，inner:内连接（交集），outer:外连接（并集）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 横向堆叠"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "横向堆叠是将两个表格按x轴（列）拼接在一起。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户编号</th>\n",
       "      <th>年龄</th>\n",
       "      <th>性别</th>\n",
       "      <th>编号</th>\n",
       "      <th>每月支出</th>\n",
       "      <th>是否愿意下载</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>男</td>\n",
       "      <td>0</td>\n",
       "      <td>6807.50</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>男</td>\n",
       "      <td>1</td>\n",
       "      <td>4780.45</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>-3.2</td>\n",
       "      <td>男</td>\n",
       "      <td>3</td>\n",
       "      <td>5011.06</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>男</td>\n",
       "      <td>5</td>\n",
       "      <td>4899.04</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>23.0</td>\n",
       "      <td>男</td>\n",
       "      <td>10</td>\n",
       "      <td>6816.02</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2230</th>\n",
       "      <td>2165</td>\n",
       "      <td>31.0</td>\n",
       "      <td>女</td>\n",
       "      <td>2165</td>\n",
       "      <td>4536.71</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2231</th>\n",
       "      <td>2168</td>\n",
       "      <td>18.0</td>\n",
       "      <td>女</td>\n",
       "      <td>2168</td>\n",
       "      <td>7072.01</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2232</th>\n",
       "      <td>2170</td>\n",
       "      <td>39.0</td>\n",
       "      <td>女</td>\n",
       "      <td>2170</td>\n",
       "      <td>4373.94</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2233</th>\n",
       "      <td>2172</td>\n",
       "      <td>26.0</td>\n",
       "      <td>女</td>\n",
       "      <td>2172</td>\n",
       "      <td>6476.80</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2234</th>\n",
       "      <td>2174</td>\n",
       "      <td>18.0</td>\n",
       "      <td>女</td>\n",
       "      <td>2174</td>\n",
       "      <td>3912.25</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2235 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户编号    年龄 性别    编号     每月支出 是否愿意下载\n",
       "0        0   NaN  男     0  6807.50    Yes\n",
       "1        1  30.0  男     1  4780.45    Yes\n",
       "2        3  -3.2  男     3  5011.06    Yes\n",
       "3        5  -1.0  男     5  4899.04     No\n",
       "4       10  23.0  男    10  6816.02     No\n",
       "...    ...   ... ..   ...      ...    ...\n",
       "2230  2165  31.0  女  2165  4536.71    Yes\n",
       "2231  2168  18.0  女  2168  7072.01     No\n",
       "2232  2170  39.0  女  2170  4373.94    Yes\n",
       "2233  2172  26.0  女  2172  6476.80     No\n",
       "2234  2174  18.0  女  2174  3912.25    Yes\n",
       "\n",
       "[2235 rows x 6 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 导入Pandas库\n",
    "import pandas as pd\n",
    "\n",
    "# 加载表格user_all_info.csv\n",
    "user_all_info = pd.read_csv(\"data/user_all_info.csv\")\n",
    "\n",
    "# 取出user_all_info的前三列\n",
    "df1 = user_all_info.iloc[:, :3]\n",
    "\n",
    "# 取出user_all_info的第四列到最后一列\n",
    "df2 = user_all_info.iloc[:, 4:]\n",
    "\n",
    "# 进行两个数据的横向外连接\n",
    "pd.concat(objs=[df1, df2], axis=1, join=\"outer\")\n",
    "\n",
    "# 进行两个数据的横向内连接\n",
    "pd.concat(objs=[df1, df2], axis=1, join=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 纵向堆叠"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "纵向堆叠是将两个数据表在y轴（行）拼接。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户编号</th>\n",
       "      <th>年龄</th>\n",
       "      <th>性别</th>\n",
       "      <th>居住类型</th>\n",
       "      <th>编号</th>\n",
       "      <th>每月支出</th>\n",
       "      <th>是否愿意下载</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>0</td>\n",
       "      <td>6807.50</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>1</td>\n",
       "      <td>4780.45</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>-3.2</td>\n",
       "      <td>男</td>\n",
       "      <td>农村</td>\n",
       "      <td>3</td>\n",
       "      <td>5011.06</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>男</td>\n",
       "      <td>农村</td>\n",
       "      <td>5</td>\n",
       "      <td>4899.04</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>23.0</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>10</td>\n",
       "      <td>6816.02</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2230</th>\n",
       "      <td>2165</td>\n",
       "      <td>31.0</td>\n",
       "      <td>女</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2165</td>\n",
       "      <td>4536.71</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2231</th>\n",
       "      <td>2168</td>\n",
       "      <td>18.0</td>\n",
       "      <td>女</td>\n",
       "      <td>城市</td>\n",
       "      <td>2168</td>\n",
       "      <td>7072.01</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2232</th>\n",
       "      <td>2170</td>\n",
       "      <td>39.0</td>\n",
       "      <td>女</td>\n",
       "      <td>城市</td>\n",
       "      <td>2170</td>\n",
       "      <td>4373.94</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2233</th>\n",
       "      <td>2172</td>\n",
       "      <td>26.0</td>\n",
       "      <td>女</td>\n",
       "      <td>农村</td>\n",
       "      <td>2172</td>\n",
       "      <td>6476.80</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2234</th>\n",
       "      <td>2174</td>\n",
       "      <td>18.0</td>\n",
       "      <td>女</td>\n",
       "      <td>农村</td>\n",
       "      <td>2174</td>\n",
       "      <td>3912.25</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2235 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户编号    年龄 性别 居住类型    编号     每月支出 是否愿意下载\n",
       "0        0   NaN  男   城市     0  6807.50    Yes\n",
       "1        1  30.0  男   城市     1  4780.45    Yes\n",
       "2        3  -3.2  男   农村     3  5011.06    Yes\n",
       "3        5  -1.0  男   农村     5  4899.04     No\n",
       "4       10  23.0  男   城市    10  6816.02     No\n",
       "...    ...   ... ..  ...   ...      ...    ...\n",
       "2230  2165  31.0  女  NaN  2165  4536.71    Yes\n",
       "2231  2168  18.0  女   城市  2168  7072.01     No\n",
       "2232  2170  39.0  女   城市  2170  4373.94    Yes\n",
       "2233  2172  26.0  女   农村  2172  6476.80     No\n",
       "2234  2174  18.0  女   农村  2174  3912.25    Yes\n",
       "\n",
       "[2235 rows x 7 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 取出user_all_info的前500行数据\n",
    "df3 = user_all_info.iloc[:500, :]\n",
    "\n",
    "# 取出user_all_info的前500行以后的数据\n",
    "df4 = user_all_info.iloc[500:, :]\n",
    "\n",
    "# 内连接纵向合并\n",
    "pd.concat(objs=[df3, df4], axis=0, join=\"inner\")\n",
    "\n",
    "# 内连接横向合并\n",
    "pd.concat(objs=[df3, df4], axis=0, join=\"outer\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 主键合并数据 pd.merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pd.merge()通过一个或多个键将两个数据集的行连接起来"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "常用参数：\n",
    "\n",
    "参数名称 | 参数说明\n",
    "--------|-------------\n",
    "left    | 数据1，接收DataFrame\n",
    "right   | 数据2，接收DataFrame\n",
    "how     | 连接方式，可选inner outer left right,默认为inner\n",
    "on      | 两个数据合并的主键(必须一致)\n",
    "left_on | 数据1用于合并的主键，默认为None\n",
    "left_on | 数据2用于合并的逐渐，默认为None\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        编号     每月支出\n",
      "2170  2170  4373.94\n",
      "2171  2171  7486.03\n",
      "2172  2172  6476.80\n",
      "2173  2173   122.55\n",
      "2174  2174  3912.25\n",
      "      用户编号 是否愿意下载\n",
      "2170  2170    Yes\n",
      "2171  2171    Yes\n",
      "2172  2172     No\n",
      "2173  2173    Yes\n",
      "2174  2174    Yes\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>每月支出</th>\n",
       "      <th>用户编号</th>\n",
       "      <th>是否愿意下载</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>6807.50</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>4780.45</td>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1959.00</td>\n",
       "      <td>2</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5011.06</td>\n",
       "      <td>3</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4557.21</td>\n",
       "      <td>4</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2182</th>\n",
       "      <td>2170</td>\n",
       "      <td>4373.94</td>\n",
       "      <td>2170</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2183</th>\n",
       "      <td>2171</td>\n",
       "      <td>7486.03</td>\n",
       "      <td>2171</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2184</th>\n",
       "      <td>2172</td>\n",
       "      <td>6476.80</td>\n",
       "      <td>2172</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2185</th>\n",
       "      <td>2173</td>\n",
       "      <td>122.55</td>\n",
       "      <td>2173</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2186</th>\n",
       "      <td>2174</td>\n",
       "      <td>3912.25</td>\n",
       "      <td>2174</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2187 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        编号     每月支出  用户编号 是否愿意下载\n",
       "0        0  6807.50     0    Yes\n",
       "1        1  4780.45     1    Yes\n",
       "2        2  1959.00     2     No\n",
       "3        3  5011.06     3    Yes\n",
       "4        4  4557.21     4     No\n",
       "...    ...      ...   ...    ...\n",
       "2182  2170  4373.94  2170    Yes\n",
       "2183  2171  7486.03  2171    Yes\n",
       "2184  2172  6476.80  2172     No\n",
       "2185  2173   122.55  2173    Yes\n",
       "2186  2174  3912.25  2174    Yes\n",
       "\n",
       "[2187 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 加载user_pay_info.csv\n",
    "pay_info = pd.read_csv(\"data/user_pay_info.csv\")\n",
    "\n",
    "# 加载user_download.csv\n",
    "download_info = pd.read_csv(\"data/user_download.csv\", encoding=\"gbk\")\n",
    "\n",
    "# 查看两个数据的最后五行\n",
    "print(pay_info.tail())\n",
    "print(download_info.tail())\n",
    "\n",
    "# 按照两表中的“编号”，“用户编号”为主键合并\n",
    "pd.merge(left=pay_info, right=download_info, left_on=\"编号\", right_on=\"用户编号\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df.join()也能主键合并，但是主键名字必须一致。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "常见参数：\n",
    "\n",
    "参数名称 | 参数说明\n",
    "--------|-----------\n",
    "other   | 接收DataFrame,参与连接的数据\n",
    "on      | 用于连接的列名\n",
    "how     | 连接方式，可选inner outer left right\n",
    "lsuffix | 左侧重叠列名后缀\n",
    "rsuffix | 右侧重叠列名后缀"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户编号</th>\n",
       "      <th>是否愿意下载</th>\n",
       "      <th>用户编号1</th>\n",
       "      <th>每月支出</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0</td>\n",
       "      <td>6807.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>1</td>\n",
       "      <td>4780.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>No</td>\n",
       "      <td>2</td>\n",
       "      <td>1959.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Yes</td>\n",
       "      <td>3</td>\n",
       "      <td>5011.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>No</td>\n",
       "      <td>4</td>\n",
       "      <td>4557.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2170</th>\n",
       "      <td>2170</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2170</td>\n",
       "      <td>4373.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2171</th>\n",
       "      <td>2171</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2171</td>\n",
       "      <td>7486.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2172</th>\n",
       "      <td>2172</td>\n",
       "      <td>No</td>\n",
       "      <td>2172</td>\n",
       "      <td>6476.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2173</th>\n",
       "      <td>2173</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2173</td>\n",
       "      <td>122.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2174</th>\n",
       "      <td>2174</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2174</td>\n",
       "      <td>3912.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2175 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户编号 是否愿意下载  用户编号1     每月支出\n",
       "0        0    Yes      0  6807.50\n",
       "1        1    Yes      1  4780.45\n",
       "2        2     No      2  1959.00\n",
       "3        3    Yes      3  5011.06\n",
       "4        4     No      4  4557.21\n",
       "...    ...    ...    ...      ...\n",
       "2170  2170    Yes   2170  4373.94\n",
       "2171  2171    Yes   2171  7486.03\n",
       "2172  2172     No   2172  6476.80\n",
       "2173  2173    Yes   2173   122.55\n",
       "2174  2174    Yes   2174  3912.25\n",
       "\n",
       "[2175 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将pay_info数据“编号”改为“用户编号”\n",
    "pay_info.rename(columns={\"编号\": \"用户编号\"}, inplace=True)\n",
    "\n",
    "# 将支付数据拼接在下载数据上\n",
    "download_info.join(pay_info, on=\"用户编号\", rsuffix='1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 清洗数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 检测与处理重复值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 去除重复记录"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df.drop_duplicates()\n",
    "常用参数：\n",
    "参数名称 | 参数说明\n",
    "--------|------------\n",
    "subset  | 表示去重的列，默认为None\n",
    "keep    | 表示重复时保留第几个数据，可选\"first\" \"last\"，默认\"first\"\n",
    "inplace | 是否在原表操作，默认为False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     Yes\n",
       "2      No\n",
       "27    NaN\n",
       "Name: 是否愿意下载, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对是否愿意下载特征进行去重\n",
    "\n",
    "# 加载用户下载数据\n",
    "download = pd.read_csv(\"data/user_download.csv\", encoding=\"gbk\")\n",
    "\n",
    "# 对是否愿意下载列进行去重\n",
    "download[\"是否愿意下载\"].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2235, 7)\n",
      "(2172, 7)\n"
     ]
    }
   ],
   "source": [
    "# 对多个特征进行去重\n",
    "\n",
    "# 加载所有用户信息表user_all_info.csv\n",
    "all_info = pd.read_csv(\"data/user_all_info.csv\")\n",
    "# 打印数据形状\n",
    "print(all_info.shape)\n",
    "# 删除\"编号\", \"用户编号\"两列都重复的数据\n",
    "all_info.drop_duplicates(subset=[\"编号\", \"用户编号\"], inplace=True)\n",
    "# 打印删除重复后数据形状\n",
    "print(all_info.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除某行或某列"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df.drop()能够删除某行或某列数据\n",
    "\n",
    "常用参数\n",
    "参数名称 | 参数说明\n",
    "--------|------------\n",
    "labels  | 表示要删除的行或列\n",
    "axis    | 表示要操作的轴，默认为0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户编号</th>\n",
       "      <th>年龄</th>\n",
       "      <th>性别</th>\n",
       "      <th>居住类型</th>\n",
       "      <th>每月支出</th>\n",
       "      <th>是否愿意下载</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>6807.50</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>4780.45</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>-3.2</td>\n",
       "      <td>男</td>\n",
       "      <td>农村</td>\n",
       "      <td>5011.06</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>男</td>\n",
       "      <td>农村</td>\n",
       "      <td>4899.04</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>23.0</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>6816.02</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2230</th>\n",
       "      <td>2165</td>\n",
       "      <td>31.0</td>\n",
       "      <td>女</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4536.71</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2231</th>\n",
       "      <td>2168</td>\n",
       "      <td>18.0</td>\n",
       "      <td>女</td>\n",
       "      <td>城市</td>\n",
       "      <td>7072.01</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2232</th>\n",
       "      <td>2170</td>\n",
       "      <td>39.0</td>\n",
       "      <td>女</td>\n",
       "      <td>城市</td>\n",
       "      <td>4373.94</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2233</th>\n",
       "      <td>2172</td>\n",
       "      <td>26.0</td>\n",
       "      <td>女</td>\n",
       "      <td>农村</td>\n",
       "      <td>6476.80</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2234</th>\n",
       "      <td>2174</td>\n",
       "      <td>18.0</td>\n",
       "      <td>女</td>\n",
       "      <td>农村</td>\n",
       "      <td>3912.25</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2172 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户编号    年龄 性别 居住类型     每月支出 是否愿意下载\n",
       "0        0   NaN  男   城市  6807.50    Yes\n",
       "1        1  30.0  男   城市  4780.45    Yes\n",
       "2        3  -3.2  男   农村  5011.06    Yes\n",
       "3        5  -1.0  男   农村  4899.04     No\n",
       "4       10  23.0  男   城市  6816.02     No\n",
       "...    ...   ... ..  ...      ...    ...\n",
       "2230  2165  31.0  女  NaN  4536.71    Yes\n",
       "2231  2168  18.0  女   城市  7072.01     No\n",
       "2232  2170  39.0  女   城市  4373.94    Yes\n",
       "2233  2172  26.0  女   农村  6476.80     No\n",
       "2234  2174  18.0  女   农村  3912.25    Yes\n",
       "\n",
       "[2172 rows x 6 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 删除all_info的编号列\n",
    "all_info.drop(labels=\"编号\", axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 检测与处理异常值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "vscode": {
     "languageId": "bat"
    }
   },
   "source": [
    "### 删除异常值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df.dropna()能够删除异常值\n",
    "\n",
    "常用参数：\n",
    "参数名称 | 参数说明\n",
    "--------|----------\n",
    "axis    | 表示要删除行或者列，接收0 1，默认为0删除行\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "用户编号       0\n",
       "年龄         6\n",
       "性别         0\n",
       "居住类型      22\n",
       "编号         0\n",
       "每月支出       0\n",
       "是否愿意下载    20\n",
       "dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看数据中的空值数量\n",
    "all_info.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2172, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户编号</th>\n",
       "      <th>年龄</th>\n",
       "      <th>性别</th>\n",
       "      <th>居住类型</th>\n",
       "      <th>编号</th>\n",
       "      <th>每月支出</th>\n",
       "      <th>是否愿意下载</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>1</td>\n",
       "      <td>4780.45</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>-3.2</td>\n",
       "      <td>男</td>\n",
       "      <td>农村</td>\n",
       "      <td>3</td>\n",
       "      <td>5011.06</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>男</td>\n",
       "      <td>农村</td>\n",
       "      <td>5</td>\n",
       "      <td>4899.04</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>23.0</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>10</td>\n",
       "      <td>6816.02</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11</td>\n",
       "      <td>-2.4</td>\n",
       "      <td>男</td>\n",
       "      <td>城市</td>\n",
       "      <td>11</td>\n",
       "      <td>7746.90</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2229</th>\n",
       "      <td>2160</td>\n",
       "      <td>15.0</td>\n",
       "      <td>女</td>\n",
       "      <td>农村</td>\n",
       "      <td>2160</td>\n",
       "      <td>5497.49</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2231</th>\n",
       "      <td>2168</td>\n",
       "      <td>18.0</td>\n",
       "      <td>女</td>\n",
       "      <td>城市</td>\n",
       "      <td>2168</td>\n",
       "      <td>7072.01</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2232</th>\n",
       "      <td>2170</td>\n",
       "      <td>39.0</td>\n",
       "      <td>女</td>\n",
       "      <td>城市</td>\n",
       "      <td>2170</td>\n",
       "      <td>4373.94</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2233</th>\n",
       "      <td>2172</td>\n",
       "      <td>26.0</td>\n",
       "      <td>女</td>\n",
       "      <td>农村</td>\n",
       "      <td>2172</td>\n",
       "      <td>6476.80</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2234</th>\n",
       "      <td>2174</td>\n",
       "      <td>18.0</td>\n",
       "      <td>女</td>\n",
       "      <td>农村</td>\n",
       "      <td>2174</td>\n",
       "      <td>3912.25</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2106 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户编号    年龄 性别 居住类型    编号     每月支出 是否愿意下载\n",
       "1        1  30.0  男   城市     1  4780.45    Yes\n",
       "2        3  -3.2  男   农村     3  5011.06    Yes\n",
       "3        5  -1.0  男   农村     5  4899.04     No\n",
       "4       10  23.0  男   城市    10  6816.02     No\n",
       "5       11  -2.4  男   城市    11  7746.90    Yes\n",
       "...    ...   ... ..  ...   ...      ...    ...\n",
       "2229  2160  15.0  女   农村  2160  5497.49     No\n",
       "2231  2168  18.0  女   城市  2168  7072.01     No\n",
       "2232  2170  39.0  女   城市  2170  4373.94    Yes\n",
       "2233  2172  26.0  女   农村  2172  6476.80     No\n",
       "2234  2174  18.0  女   农村  2174  3912.25    Yes\n",
       "\n",
       "[2106 rows x 7 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看所有信息\n",
    "print(all_info.shape)\n",
    "\n",
    "# 删除出现空值的行\n",
    "all_info.dropna(axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 替换异常值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df.fillna()能够替换异常值\n",
    "\n",
    "常用参数\n",
    "参数名称 | 参数说明\n",
    "-------|--------\n",
    "value  | 要替换的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看每月支出列的空值数量\n",
    "all_info.loc[:, \"每月支出\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 求每月支出的均值\n",
    "mean_num = all_info.loc[:, \"每月支出\"].mean()\n",
    "\n",
    "# 空值填充为均值\n",
    "all_info.loc[:, \"每月支出\"].fillna(mean_num, inplace=True)\n",
    "\n",
    "# 查看空值数量\n",
    "all_info.loc[:, \"每月支出\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python-data-analysis",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
